SELECT SOG.SOG_CODFIS,SOG.SOG_PARIVA, CON.CON_ANNO,CON.CON_ID CODICE_CONTRATTO,CON.TCON_DES TIPO_CONTRATTO,
CON.SER_DES,
UBI.DECODE_INDID INDIRIZZO_FORNITURA,
CASE WHEN CON.STA_IDSYS=2 THEN 'ATTIVO' ELSE 'CHIUSO' END STATO_CONTRATTO,
(SELECT DISTINCT MAX (val.vpxco_val)
                        FROM UE_ASP_VALPARXCUFOT val
                       WHERE val.cufot_vernum = cot.ver_num
                         AND val.parot_id LIKE '%NOME%OFFERT%'
                         AND VAL.STA_IDSYS=2
                         AND VAL.VER_DTFINE>=CON.VER_DTFINE ) NOME_OFFERTA,
TRUNC(CON.CON_DTINIGES) DATA_INIZIO,TRUNC(CON.VER_DTFINE) DATA_CESSAZIONE,RG.DECODE_INDIDSPEFAT INDIRIZZO_SPEDIZIONE,
ii.iva_des iva_des1,ii.iva_des1 iva_des2,
                   (SELECT DISTINCT iva.iva_ali
                               FROM iva
                              WHERE iva.iva_id = Ii.iva_id
                                AND iva.az_id = ii.az_id) iva_ali1,
                   (SELECT DISTINCT iva.iva_ali
                               FROM iva
                              WHERE iva.iva_id = Ii.iva_id1
                                AND iva.az_id = Ii.az_id) iva_ali2,
                                 (select a.ccon_valvis from v_valcxcuf a where ubi.cuf_id = a.cuf_id 
 and a.az_id = ubi.az_id and a.STA_IDSYS =2 and a.VER_DTFINE = to_date('31129999','ddmmyyyy')
and ccon_id = 35 ) "Doppio invio fat.",
rg.RAGF_EMAILSPEFAT   "Indirizzo e_mail", 
 rg.ragf_id,
                                

(SELECT MAX(ELEC.FU )FROM UE_ASP_CONSANNO_ELE ELEC WHERE 
 ELEC.CON_ANNO=CON.CON_ANNO
AND ELEC.CON_ID=CON.CON_ID) FU_ELE,

(SELECT MAX(ELEC.F1 )FROM UE_ASP_CONSANNO_ELE ELEC WHERE 
 ELEC.CON_ANNO=CON.CON_ANNO
AND ELEC.CON_ID=CON.CON_ID) F1_ELE,

(SELECT MAX(ELEC.F2 )FROM UE_ASP_CONSANNO_ELE ELEC WHERE 
 ELEC.CON_ANNO=CON.CON_ANNO
AND ELEC.CON_ID=CON.CON_ID) F2_ELE,

(SELECT MAX(ELEC.F3 )FROM UE_ASP_CONSANNO_ELE ELEC WHERE 
 ELEC.CON_ANNO=CON.CON_ANNO
AND ELEC.CON_ID=CON.CON_ID) F3_ELE,

(SELECT MAX(ELEC.PEAKON )FROM UE_ASP_CONSANNO_ELE ELEC WHERE 
 ELEC.CON_ANNO=CON.CON_ANNO
AND ELEC.CON_ID=CON.CON_ID) PEAKON_ELE,

(SELECT MAX(ELEC.PEAKOFF )FROM UE_ASP_CONSANNO_ELE ELEC WHERE 
 ELEC.CON_ANNO=CON.CON_ANNO

AND ELEC.CON_ID=CON.CON_ID) PEAKOFF_ELE,

(SELECT MAX(ELEG.FU )FROM UE_ASP_CONSANNO_GAS ELEG WHERE 
 ELEG.CON_ANNO=CON.CON_ANNO
AND ELEG.CON_ID=CON.CON_ID
) FU_GAS, 

(SELECT MAX(ATT.TENSIONE_VOLT) FROM UE_ASP_UE_DETT_ATTACCOELE ATT WHERE ATT.ATT_ID=UBI.ATT_ID ) TENSIONE,

(SELECT MAX(ATT.TIPO_FORNITURA_EE) FROM UE_ASP_UE_DETT_ATTACCOELE ATT WHERE ATT.ATT_ID=UBI.ATT_ID ) LIVELLO_TENSIONE, 
 

(SELECT MAX(ATT.POT_IMPEGNATA) FROM UE_ASP_UE_DETT_ATTACCOELE ATT WHERE ATT.ATT_ID=UBI.ATT_ID ) POTENZA_IMPEGNATA,  
 
 (SELECT MAX(ATT.POT_DISPONIBILE) FROM UE_ASP_UE_DETT_ATTACCOELE ATT WHERE ATT.ATT_ID=UBI.ATT_ID ) POTENZA_DISPONIBILE,    
 

GMIS.TSM_DES TIPO_MISURATORE,

  
UBI.ATT_IDEXT POD, 
                             
vrg.rfrp_fltipopag, vrg.mpag_id, vrg.mpag_des,
                   vrg.abicab_abi, vrg.abicab_cab, vrg.abicab_des,
                   vrg.rfrp_cc, vrg.rfrp_cin, vrg.rfrp_codfis, vrg.rfrp_iban,
                   vrg.banapp_iban,
sog.SOG_EMAIL,
CA.CTS_FOG "Foglio",CA.CTS_PAR "Particella",CA.CTS_SUBALT "Subalterno",GMIS.TSRC_ID CODICE_TIPO_MISURATORE
                   
                   

FROM 
ue_v_contratto CON,V_SOGGETTO SOG,UE_ASP_V_CONUBIFRN UBI,V_CUFOT COT,ue_v_ragfat RG,
(select * from V_CONIVA I where az_id='01' AND I.STA_IDSYS IN (2,31) AND I.VER_DTFINE>to_date('17/09/2011','dd/mm/yyyy')) ii ,
ue_v_ragfatregpag vrg,UE_ASP_GMISSMIS GMIS,
UE_ASP_CATASTO CA,UE_ASP_PAREDI PA

WHERE CON.STA_IDSYS IN (2,31)
AND SOG.SOG_ID=CON.SOG_ID
AND CON.CON_ANNO=UBI.CON_ANNO
AND CON.CON_ID=UBI.CON_ID
AND UBI.CUF_ID=COT.CUF_ID
AND cot.comser_id IN (1,27, 16)
AND (con.ver_dtfine = cot.ver_dtfine OR cot.ver_dtfine = TO_DATE ('31/12/9999', 'DD/MM/YYYY'))
AND cot.sta_idsys = 2
AND RG.RAGF_ID=CON.RAGF_ID
AND RG.STA_IDSYS IN (2,31)
AND Ii.CON_ANNO(+)=CON.CON_ANNO
AND ii.CON_ID(+)=CON.CON_ID


AND VRG.RAGF_ID=RG.RAGF_ID
AND VRG.STA_IDSYS IN (2,31)
AND GMIS.CON_ANNO=CON.CON_ANNO
AND GMIS.CON_ID=CON.CON_ID
and con.az_id=sog.az_id
and con.az_id = ubi.az_id
and con.az_id = cot.az_id
and con.az_id = rg.az_id

and con.az_id = vrg.az_id
and con.az_id = gmis.az_id
and ca.az_id(+)='01'
AND PA.SITO_ID = UBI.SITO_ID
AND PA.PE_ID=CA.PE_ID(+)


 